use "Output Data\contracts_analysis.dta", clear

rename *, lower
drop if year < 2000

egen comp_agency = group(companyid agencyid)
egen agency_year = group(agencyid year)
egen comp_year = group(companyid year)
egen agency_ind = group(agencyid naics)
egen agency_ind_year = group(agencyid naics year)
bysort agencyid naics: egen ind_contracts = max(n_contracts)

tsset comp_agency year

gen log_ob = ln(total_obligation + 1)
replace log_ob = 0 if missing(log_ob)
gen contract_flag = 0
replace contract_flag = 1 if n_contracts > 0 & !missing(n_contracts)

gen election = 0
replace election = 1 if year == 2005
replace election = 1 if year == 2009
replace election = 1 if year == 2013
replace election = 1 if year == 2017

gen elect_tm2 = 0
replace elect_tm2 = 1 if year == 2003 | year == 2007 | year == 2011 | year == 2015 | year == 2019
gen elect_tm1 = 0
replace elect_tm1 = 1 if year == 2004 | year == 2008 | year == 2012 | year == 2016
gen elect_t = 0
replace elect_t = 1 if election == 1
gen elect_tp1 = 0
replace elect_tp1 = 1 if year == 2002 | year == 2006 | year == 2010 | year == 2014 | year == 2018
gen elect_tp2 = 0
replace elect_tp2 = 1 if year == 2003 | year == 2007 | year == 2011 | year == 2015 | year == 2019
gen elect_tp3 = 0
replace elect_tp3 = 1 if year > 2005 + 2

gen event = 0
replace event = 1 if appointment == 1
replace event = 0 if year < 2000
replace event = 0 if year > 2018
bysort comp_agency: egen first_event_drop = min(year) if event == 1
bysort comp_agency: egen first_event = mean(first_event_drop)
drop first_event_drop
bysort comp_agency: egen first_year = min(year) if !missing(first_event)
replace first_year = 2000 if first_year < 2000
replace event = . if first_year > first_event - 2 & !missing(first_event) /*Psuedo drop these observations*/

gen event_tm2 = 0 if !missing(event)
replace event_tm2 = 1 if F2.event == 1 & !missing(event)
gen event_tm1 = 0 if !missing(event)
replace event_tm1 = 1 if F.event == 1 & !missing(event)
gen event_t = 0 if !missing(event)
replace event_t = 1 if event == 1 & !missing(event)
gen event_tp1 = 0 if !missing(event)
replace event_tp1 = 1 if L.event == 1 & !missing(event)
gen event_tp2 = 0 if !missing(event)
replace event_tp2 = 1 if L2.event == 1 & !missing(event)
gen event_tp3 = 0 if !missing(event)
replace event_tp3 = 1 if year > first_event + 2 & !missing(event)







reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store a1

reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(comp_agency) vce(cluster comp_agency)
estimates store m1

reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year >= 2000 & year <= 2018 & !missing(connect_num) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m2

reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(comp_agency) vce(cluster comp_agency)
estimates store m3

reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m4

#delimit ;
esttab m1 m2 m3 m4 output.csv, 
	starlevels(* 0.10 ** 0.05 *** 0.01)
	cells(b(star fmt(%9.6f)) t(par fmt(%9.2f)))
	ar2
	replace;
#delimit cr

sum contract_flag if _est_m1 == 1
sum log_ob if _est_m3 == 1



drop event first_event first_year event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3
gen event = 0
replace event = 1 if appointment_top == 1
replace event = 0 if year > 2018
bysort comp_agency: egen first_event_drop = min(year) if event == 1
bysort comp_agency: egen first_event = mean(first_event_drop)
drop first_event_drop
bysort comp_agency: egen first_year = min(year) if !missing(first_event)
replace first_year = 2000 if first_year < 2000
replace event = . if first_year > first_event - 2 & !missing(first_event) /*Psuedo drop these observations*/

gen event_tm2 = 0 if !missing(event)
replace event_tm2 = 1 if F2.event == 1 & !missing(event)
gen event_tm1 = 0 if !missing(event)
replace event_tm1 = 1 if F.event == 1 & !missing(event)
gen event_t = 0 if !missing(event)
replace event_t = 1 if event == 1 & !missing(event)
gen event_tp1 = 0 if !missing(event)
replace event_tp1 = 1 if L.event == 1 & !missing(event)
gen event_tp2 = 0 if !missing(event)
replace event_tp2 = 1 if L2.event == 1 & !missing(event)
gen event_tp3 = 0 if !missing(event)
replace event_tp3 = 1 if year > first_event + 2 & !missing(event)

reghdfe contract_flag c.event_tm2##c.elect_tm2 c.event_tm1##c.elect_tm1 c.event_t##c.elect_t c.event_tp1##c.elect_tp1 c.event_tp2##c.elect_tp2 c.event_tp3##c.elect_tp3 if year <= 2018 & !missing(connect_num) & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m1

reghdfe log_ob c.event_tm2##c.elect_tm2 c.event_tm1##c.elect_tm1 c.event_t##c.elect_t c.event_tp1##c.elect_tp1 c.event_tp2##c.elect_tp2 c.event_tp3##c.elect_tp3 if year <= 2018 & !missing(connect_num) & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m2

#delimit ;
esttab m1 m2 output.csv, append
	starlevels(* 0.10 ** 0.05 *** 0.01)
	cells(b(star fmt(%9.6f)) t(par fmt(%9.2f)))
	ar2;
#delimit cr

sum contract_flag if _est_m1 == 1
sum log_ob if _est_m2 == 1



drop event first_event first_year event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3
gen event = 0
replace event = 1 if departure == 1
replace event = 0 if year > 2018
bysort comp_agency: egen first_event_drop = min(year) if event == 1
bysort comp_agency: egen first_event = mean(first_event_drop)
drop first_event_drop
bysort comp_agency: egen first_year = min(year) if !missing(first_event)
replace first_year = 2000 if first_year < 2000
replace event = . if first_year > first_event - 2 & !missing(first_event) /*Psuedo drop these observations*/

gen event_tm2 = 0 if !missing(event)
replace event_tm2 = 1 if F2.event == 1 & !missing(event)
gen event_tm1 = 0 if !missing(event)
replace event_tm1 = 1 if F.event == 1 & !missing(event)
gen event_t = 0 if !missing(event)
replace event_t = 1 if event == 1 & !missing(event)
gen event_tp1 = 0 if !missing(event)
replace event_tp1 = 1 if L.event == 1 & !missing(event)
gen event_tp2 = 0 if !missing(event)
replace event_tp2 = 1 if L2.event == 1 & !missing(event)
gen event_tp3 = 0 if !missing(event)
replace event_tp3 = 1 if year > first_event + 2 & !missing(event)

qui: reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store a1

reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(comp_agency) vce(cluster comp_agency)
estimates store m1

reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m2

reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(comp_agency) vce(cluster comp_agency)
estimates store m3

reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m4

#delimit ;
esttab m1 m2 m3 m4 output.csv, append
	starlevels(* 0.10 ** 0.05 *** 0.01)
	cells(b(star fmt(%9.6f)) t(par fmt(%9.2f)))
	ar2;
#delimit cr

sum contract_flag if _est_m1 == 1
sum log_ob if _est_m3 == 1



drop event first_event first_year event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3
gen event = 0
replace event = 1 if appointment_dw == 1
replace event = 0 if year > 2018
bysort comp_agency: egen first_event_drop = min(year) if event == 1
bysort comp_agency: egen first_event = mean(first_event_drop)
drop first_event_drop
bysort comp_agency: egen first_year = min(year) if !missing(first_event)
replace first_year = 2000 if first_year < 2000
replace event = . if first_year > first_event - 2 & !missing(first_event) /*Psuedo drop these observations*/

gen event_tm2 = 0 if !missing(event)
replace event_tm2 = 1 if F2.event == 1 & !missing(event)
gen event_tm1 = 0 if !missing(event)
replace event_tm1 = 1 if F.event == 1 & !missing(event)
gen event_t = 0 if !missing(event)
replace event_t = 1 if event == 1 & !missing(event)
gen event_tp1 = 0 if !missing(event)
replace event_tp1 = 1 if L.event == 1 & !missing(event)
gen event_tp2 = 0 if !missing(event)
replace event_tp2 = 1 if L2.event == 1 & !missing(event)
gen event_tp3 = 0 if !missing(event)
replace event_tp3 = 1 if year > first_event + 2 & !missing(event)

qui: reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store a1

reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(comp_agency) vce(cluster comp_agency)
estimates store m1

reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m2

reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(comp_agency) vce(cluster comp_agency)
estimates store m3

reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m4

#delimit ;
esttab m1 m2 m3 m4 using output.csv, append
	starlevels(* 0.10 ** 0.05 *** 0.01)
	cells(b(star fmt(%9.6f)) t(par fmt(%9.2f)))
	ar2;
#delimit cr

sum contract_flag if _est_m1 == 1
sum log_ob if _est_m3 == 1



drop event first_event first_year event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3
gen event = 0
replace event = 1 if departure_dw == 1
replace event = 0 if year > 2018
bysort comp_agency: egen first_event_drop = min(year) if event == 1
bysort comp_agency: egen first_event = mean(first_event_drop)
drop first_event_drop
bysort comp_agency: egen first_year = min(year) if !missing(first_event)
replace first_year = 2000 if first_year < 2000
replace event = . if first_year > first_event - 2 & !missing(first_event) /*Psuedo drop these observations*/

gen event_tm2 = 0 if !missing(event)
replace event_tm2 = 1 if F2.event == 1 & !missing(event)
gen event_tm1 = 0 if !missing(event)
replace event_tm1 = 1 if F.event == 1 & !missing(event)
gen event_t = 0 if !missing(event)
replace event_t = 1 if event == 1 & !missing(event)
gen event_tp1 = 0 if !missing(event)
replace event_tp1 = 1 if L.event == 1 & !missing(event)
gen event_tp2 = 0 if !missing(event)
replace event_tp2 = 1 if L2.event == 1 & !missing(event)
gen event_tp3 = 0 if !missing(event)
replace event_tp3 = 1 if year > first_event + 2 & !missing(event)

qui: reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store a1

reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(comp_agency) vce(cluster comp_agency)
estimates store m1

reghdfe contract_flag event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m2

reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(comp_agency) vce(cluster comp_agency)
estimates store m3

reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num_dw) & _est_a1 == 1 & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m4

#delimit ;
esttab m1 m2 m3 m4 using output.csv, append
	starlevels(* 0.10 ** 0.05 *** 0.01)
	cells(b(star fmt(%9.6f)) t(par fmt(%9.2f)))
	ar2;
#delimit cr

sum contract_flag if _est_m1 == 1
sum log_ob if _est_m3 == 1














/*  Table 1: Summary Stats  */

drop event first_event first_year event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3
gen event = 0
replace event = 1 if appointment == 1
replace event = 0 if year > 2018
bysort comp_agency: egen first_event_drop = min(year) if event == 1
bysort comp_agency: egen first_event = mean(first_event_drop)
drop first_event_drop
bysort comp_agency: egen first_year = min(year) if !missing(first_event)
replace first_year = 2000 if first_year < 2000
replace event = . if first_year > first_event - 2 & !missing(first_event) /*Psuedo drop these observations*/

gen event_tm2 = 0 if !missing(event)
replace event_tm2 = 1 if F2.event == 1 & !missing(event)
gen event_tm1 = 0 if !missing(event)
replace event_tm1 = 1 if F.event == 1 & !missing(event)
gen event_t = 0 if !missing(event)
replace event_t = 1 if event == 1 & !missing(event)
gen event_tp1 = 0 if !missing(event)
replace event_tp1 = 1 if L.event == 1 & !missing(event)
gen event_tp2 = 0 if !missing(event)
replace event_tp2 = 1 if L2.event == 1 & !missing(event)
gen event_tp3 = 0 if !missing(event)
replace event_tp3 = 1 if year > first_event + 2 & !missing(event)

gen connect_num_total = connect_num + connect_num_dw
gen connect_flag = 0 if !missing(connect_num)
replace connect_flag = 1 if connect_num > 0 & !missing(connect_num)
gen connect_flag_dw = 0 if !missing(connect_num_dw)
replace connect_flag_dw = 1 if connect_num_dw > 0 & !missing(connect_num_dw)
gen connect_flag_total = 0 if !missing(connect_num_total)
replace connect_flag_total = 1 if connect_num_total > 0 & !missing(connect_num_total)

qui: reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m1
qui: reghdfe log_ob event_tm2 event_tm1 event_t event_tp1 event_tp2 event_tp3 if year <= 2018 & !missing(connect_num) & ind_contracts > 0 & !missing(ind_contracts), a(agency_ind_year comp_year comp_agency) vce(cluster comp_agency)
estimates store m2

bysort comp_year: egen connect_comp_flag = max(connect_flag) if _est_m1 == 1
bysort comp_year _est_m1: replace connect_comp_flag = . if _n ~= 1
bysort comp_year: egen connect_comp_flag_dw = max(connect_flag_dw) if _est_m1 == 1
bysort comp_year _est_m1: replace connect_comp_flag_dw = . if _n ~= 1
bysort comp_year: egen connect_comp_flag_total = max(connect_flag_total) if _est_m1 == 1
bysort comp_year _est_m1: replace connect_comp_flag_total = . if _n ~= 1

sum connect_comp_flag connect_comp_flag_dw connect_comp_flag_total
drop connect_comp_flag connect_comp_flag_dw connect_comp_flag_total

bysort comp_year: egen connect_comp_flag = max(connect_flag) if _est_m2 == 1
bysort comp_year _est_m2: replace connect_comp_flag = . if _n ~= 1
bysort comp_year: egen connect_comp_flag_dw = max(connect_flag_dw) if _est_m2 == 1
bysort comp_year _est_m2: replace connect_comp_flag_dw = . if _n ~= 1
bysort comp_year: egen connect_comp_flag_total = max(connect_flag_total) if _est_m2 == 1
bysort comp_year _est_m2: replace connect_comp_flag_total = . if _n ~= 1

sum connect_comp_flag connect_comp_flag_dw connect_comp_flag_total
drop connect_comp_flag connect_comp_flag_dw connect_comp_flag_total

sum connect_flag connect_flag_dw connect_flag_total if _est_m1 == 1
sum connect_flag connect_flag_dw connect_flag_total if _est_m2 == 1








/* TABLE 4 */

bysort agencyid: egen n_agency_contracts = sum(n_contracts) if _est_a1 == 1
bysort agencyid _est_a1: replace n_agency_contracts = . if _n ~= 1
bysort agencyid: egen ob_agency_contracts = sum(total_obligation) if _est_a1 == 1
bysort agencyid _est_a1: replace ob_agency_contracts = . if _n ~= 1

tab agencyid, sum(n_agency_contracts)
table agencyid, statistic(mean n_agency_contracts ob_agency_contracts) nformat(%22.0g)

